


ALTER      proc sp_eng_pull_report
-- Input parameter is process StepID;
@process_step_id int,
@requestor_name varchar(15),
@date_From varchar(10),
@date_To   varchar(10)
as

/*variable to store the values
*/
declare @qty_in int, @qty_out int, @yield float
declare @date_in datetime, @date_out datetime
declare @temp_lot_id int



declare flowCursor CURSOR FOR
select * from fn_get_process_flow_by_id(@process_step_id)

declare @@temp_process_step_id bigint
declare @@temp_ps_type varchar(20)
declare @@temp_ps_type_name varchar(20)
open flowCursor 
fetch next from flowCursor into @@temp_process_step_id,@@temp_ps_type,@@temp_ps_type_name

print @@temp_process_step_id

while @@fetch_status = 0
begin
-- Cursor to store the Lot_Number and Lot_ID
-- If process_step_id variable is 0 store all lot_numbers and 
declare @lotCursor CURSOR
set @lotCursor = CURSOR for
select  Lot_ID, Lot_Number from Lot_Table where ProcessStep_ID = @@temp_process_step_id


declare @lot_no varchar(50)
declare @lot_id int
declare @results Table(Lot_Number varchar(20),Product varchar(50),Requestor_Name varchar(20),Pull_Qty int, Return_Qty int,Pull_Date datetime,Return_Date datetime,Process_Step varchar(50),Vendor varchar(50))

open @lotCursor 
fetch next from @lotCursor
into @lot_id, @lot_no

while @@fetch_status = 0
begin
	
	print '1 Lot = '+@lot_no
	print '1 Lot_ID = '
	print @lot_id
	declare @procstepCursor CURSOR
	set @procstepCursor = CURSOR for
	select ProcessStep_ID,Lot_ID from Lot_Table where Lot_Number = @lot_no and Lot_ID = @lot_id

	
	declare @type varchar(50)
	declare @testType varchar(50)
	declare @location varchar(50)
	declare @product varchar(50)
	declare @eng_pull_id int
	declare @req_name varchar(50)
	declare @pull_date datetime
	declare @return_date datetime
	declare @pull_qty int
	declare @return_qty int

	declare @temp_step int
        open @procstepCursor
	fetch next from @procstepCursor into @temp_step,@temp_lot_id
	while @@fetch_status = 0
	begin
	      
	      if(@requestor_name='0' and @date_From = '0' and @date_To ='0' )	
	      begin	
	      select @eng_pull_id=EngPull_ID,@req_name=EP_Requestor_Name,@pull_qty=EP_Qty,@return_qty=EP_Return_Qty,@pull_date=convert(varchar,[Timestamp],101),@return_date=convert(varchar,EP_Return_Date,101) from Eng_Pull where lot_id=@temp_lot_id 	
	      end
		
	      else if(@requestor_name<>'0' and @date_From = '0' and @date_To ='0' )	
	      begin	
	      select @eng_pull_id=EngPull_ID,@req_name=EP_Requestor_Name,@pull_qty=EP_Qty,@return_qty=EP_Return_Qty,@pull_date=convert(varchar,[Timestamp],101),@return_date=convert(varchar,EP_Return_Date,101) from Eng_Pull where lot_id=@temp_lot_id and EP_Requestor_Name like @requestor_name+'%';
	      end
	
	      else if(@requestor_name='0' and @date_From <> '0' and @date_To <> '0' )	
	      begin	
	      select @eng_pull_id=EngPull_ID,@req_name=EP_Requestor_Name,@pull_qty=EP_Qty,@return_qty=EP_Return_Qty,@pull_date=convert(varchar,[Timestamp],101),@return_date=convert(varchar,EP_Return_Date,101) from Eng_Pull where lot_id=@temp_lot_id and [Timestamp]>=@date_From and [Timestamp]<=@date_To
	      end
   
	      else if(@requestor_name<>'0' and @date_From <> '0' and @date_To <> '0' )	
	      begin	
	      select @eng_pull_id=EngPull_ID,@req_name=EP_Requestor_Name,@pull_qty=EP_Qty,@return_qty=EP_Return_Qty,@pull_date=convert(varchar,[Timestamp],101),@return_date=convert(varchar,EP_Return_Date,101) from Eng_Pull where lot_id=@temp_lot_id and [Timestamp]>=@date_From and [Timestamp]<=@date_To and EP_Requestor_Name like @requestor_name+'%';
	      end	 		

	
	      if(@eng_pull_id is not null)				
 	      begin	

		select @type = PS_Type_Name,@testType=PS_Type from Process_Step where ProcessStep_ID = @temp_step
		select @location = (select L_Vendor_Name from location,lot_table where lot_id=@temp_lot_id and lot_table.location_Id = location.location_Id)
		select @product =(select part_number from product where  processStep_id = @temp_step)
		insert into @results(Lot_Number,Product,Requestor_Name,Pull_Qty,Return_Qty,Pull_Date,Return_Date,Process_Step,Vendor) values(@lot_no,@product, @req_name, @pull_qty, @return_qty, @pull_date, @return_date,@type,@location) 				
		--print 'Type = '+@type
		end
		set @eng_pull_id = null
		fetch next from @procstepCursor into @temp_step,@temp_lot_id
	end
	close @procstepCursor
	deallocate @procstepCursor
	fetch next from @lotCursor
	into @lot_id, @lot_no
end
close @lotCursor
deallocate @lotCursor
fetch next from flowCursor into @@temp_process_step_id,@@temp_ps_type,@@temp_ps_type_name
end
close flowCursor
deallocate flowCursor
select *from @results



